#!/usr/bin/env python3
"""
JH子系统数据库结构修复脚本
修复统计表字段不匹配的问题
"""

import sqlite3
import sys
import os
from pathlib import Path

# 添加项目根目录到路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

def main():
    """主函数"""
    print("🔧 JH子系统数据库结构修复器")
    print("="*50)
    
    db_path = "jh_job_analytics.db"
    
    try:
        with sqlite3.connect(db_path) as conn:
            print("📊 检查当前数据库结构...")
            
            # 检查jobs表记录数
            cursor = conn.execute('SELECT COUNT(*) FROM jobs')
            job_count = cursor.fetchone()[0]
            print(f"✅ Jobs表包含 {job_count:,} 条记录")
            
            # 删除旧的统计表
            print("🗑️ 删除旧的统计表...")
            conn.execute('DROP TABLE IF EXISTS skill_stats')
            conn.execute('DROP TABLE IF EXISTS location_stats')
            
            # 重新创建正确的统计表结构
            print("📝 创建新的统计表结构...")
            
            # 技能统计表
            conn.execute('''
            CREATE TABLE skill_stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                skill_name TEXT UNIQUE,
                job_count INTEGER,
                avg_salary REAL,
                created_date TEXT DEFAULT CURRENT_TIMESTAMP
            )
            ''')
            
            # 地区统计表 - 修正字段名
            conn.execute('''
            CREATE TABLE location_stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_location TEXT UNIQUE,
                job_count INTEGER,
                avg_salary REAL,
                created_date TEXT DEFAULT CURRENT_TIMESTAMP
            )
            ''')
            
            print("✅ 统计表结构修复完成")
            
            # 生成基础统计数据
            print("📈 生成基础统计数据...")
            
            # 插入技能统计 - 修正版本，只统计有薪资数据的记录
            conn.execute("DELETE FROM skill_stats")
            conn.execute('''
            INSERT INTO skill_stats (skill_name, job_count, avg_salary)
            SELECT 
                'Python' as skill_name,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs 
            WHERE (job_skills LIKE '%python%' OR job_title LIKE '%Python%') AND salary_year_avg IS NOT NULL
            UNION
            SELECT 
                'SQL' as skill_name,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs 
            WHERE (job_skills LIKE '%sql%' OR job_title LIKE '%SQL%') AND salary_year_avg IS NOT NULL
            UNION
            SELECT 
                'Java' as skill_name,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs 
            WHERE (job_skills LIKE '%java%' OR job_title LIKE '%Java%') AND salary_year_avg IS NOT NULL
            UNION
            SELECT 
                'JavaScript' as skill_name,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs 
            WHERE (job_skills LIKE '%javascript%' OR job_title LIKE '%JavaScript%') AND salary_year_avg IS NOT NULL
            UNION
            SELECT 
                'Machine Learning' as skill_name,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs 
            WHERE (job_skills LIKE '%machine learning%' OR job_title LIKE '%Machine Learning%') AND salary_year_avg IS NOT NULL
            ''')
            
            # 插入地区统计 - 修正字段名，只统计有薪资数据的记录
            conn.execute("DELETE FROM location_stats")
            conn.execute('''
            INSERT INTO location_stats (job_location, job_count, avg_salary)
            SELECT 
                job_location,
                COUNT(*) as job_count,
                AVG(salary_year_avg) as avg_salary
            FROM jobs
            WHERE job_location IS NOT NULL AND job_location != '' AND salary_year_avg IS NOT NULL
            GROUP BY job_location
            ORDER BY job_count DESC
            LIMIT 50
            ''')
            
            conn.commit()
            print("✅ 基础统计数据生成完成")
            
            # 验证修复结果
            cursor = conn.execute('SELECT COUNT(*) FROM skill_stats')
            skill_count = cursor.fetchone()[0]
            
            cursor = conn.execute('SELECT COUNT(*) FROM location_stats')
            location_count = cursor.fetchone()[0]
            
            print(f"\n📊 修复后统计:")
            print(f"  技能统计记录: {skill_count}")
            print(f"  地区统计记录: {location_count}")
            
            print("\n🚀 数据库结构修复完成！现在可以正常使用JH子系统了")
            
    except Exception as e:
        print(f"❌ 修复数据库结构失败: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main() 